<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  
  <title>Jailer - Design</title>
  <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
  <meta name="description" content="Data Export Tool" />
  <meta name="keywords" content="data export referential integrity java jdbc dbms" />
  <link rel="stylesheet" type="text/css" href="styles.css" />
  <link rel="shortcut icon" href="favicon.ico" />
</head>

<body left-margin="0" top-margin="0">
  <div align="left">
    <table style="text-align: left; position: absolute; left: 0pt; top: 0pt;" border="0"
    cellpadding="0" cellspacing="0" height="407" width="100%">
      <tbody>
        <tr>
          <td colspan="2" class="bannerbackground" height="33" width="100%">
            <table border="0" cellpadding="4" cellspacing="0" width="100%">
              <tbody>
                        <tr>
                          <td><a href="https://github.com/Wisser/Jailer"><img src="logo.png" hspace="10" /></td></a>

                          <td class="slogan" width="100%"></td>

                          <td style="text-align: right; width: 100%;"><table border="0" cellpadding="0" cellspacing="0">
                      <tbody>
                        <tr>
                           <td><a href="https://github.com/Wisser/Jailer"><img src="GitHub-Mark-64px.png" title="GitHub" /></a> </td></tr><tr style="height: 100%;"><td>&nbsp;</td>
                        </tr>
                      </tbody>
                    </table> </td>
                        </tr>
</tbody></table>
                <tr>
                  <td colspan="2" class="nav1background"  width="100%">&nbsp;
                  <b><font color="#FFFFFF"><a href="home.htm" target="_self" class=
                  "tlink">&nbsp;Home&nbsp;</a> <a href="quicktour.htm" target=
                  "_self" class="tlink">&nbsp;Quick Tour&nbsp;</a> <a href=
                  "exporting-data.htm" target="_self" class="tlink">&nbsp;Tutorial&nbsp;</a> 
                  <a href="data-browsing.html" target="_self" class=
                  "tlink">&nbsp;Data Browser&nbsp;</a> <a class="tlink" href="videos.html" target="_self">&nbsp;Videos&nbsp;</a> <a href="faq.html" target=
                  "_self" class="tlink">&nbsp;FAQ&nbsp;</a> <a href="api.html"
                  target="_self" class="tlink">&nbsp;API&nbsp;</a> <a href=
                  "design.htm" target="_self" class="tlinkA">&nbsp;Documentation&nbsp;</a> 
                  <a href=
                  "http://sourceforge.net/forum/?group_id=197260" target="_self" class=
                  "tlink">&nbsp;Forum&nbsp;</a> <a href=
                  "http://sourceforge.net/project/showfiles.php?group_id=197260" target=
                  "_self" class="tlink">&nbsp;Download&nbsp;</a>  &nbsp;&nbsp;&nbsp;
                  &nbsp;&nbsp;&nbsp;</font></b></td>
                </tr>

                <tr>
                  <td colspan="2" class="spacer" width="100%"></td> </tr><tr><td class="lmenucontainer">&nbsp;</td>
                </tr>

                <tr>
                  <td class="lmenucontainer" height="100%" valign="top" width="14%">
                    <div align="center">
                      <center>
                        <table border="0" cellpadding="0" cellspacing="0" width="100%">
                          <tbody>
                            <tr>
                              <td height="6px" width="100%"> </td>
                            </tr>
                          </tbody>
                        </table>
                      </center>
                    </div>

                    <ul>
                      <li><a href="design.htm" target="_self" class=
                      "mlinkA">Algorithm</a> </li>

                      <li><a href="file-formats.htm" target="_self" class="mlink">File
                      Formats</a> </li>
                    </ul>

                    <table border="0" cellpadding="5" cellspacing="0" width="100%">
                      <tbody>
                        <tr>
                          <td class="content2background"><img style=
                          "width: 160px; height: 1px;" alt="" src=
                          "architecture-Dateien/vgradp.gif" /></td>
                        </tr>
                      </tbody>
                    </table>

                    <p>&nbsp;</p>
                  </td>

                  <td class="contentbackground" height="418" valign="top" width="86%">
                    <div align="right">
                      <table border="0" cellpadding="0" cellspacing="0" height="542"
                      width="98%">
                        <tbody>
                          <tr>
                            <td class="contentbackground" height="21" width="100%">
                            <br /></td>
                          </tr>

                          <tr>
                            <td class="content" height="520" valign="top" width="100%">
                              <h4><a name="modell_des_entitaetsgraphen">The Entity
                              Graph</a> &nbsp;</h4>During the export, a graph of the entities is created in the database. Three tables are created for this purpose:

                              <div class="level3">
                                <ul>
                                  <li class="level1">
                                    <div class="li">
                                      <strong>JAILER_ENTITYGRAPH</strong> the graph<br />
                                    </div>
                                  </li>

                                  <li class="level1">
                                    <div class="li">
                                      <strong>JAILER_ENTITY</strong> entity in the graph<br />
                                    </div>
                                  </li>

                                  <li class="level1">
                                    <div class="li">
                                      <strong>JAILER_DEPENDENCY</strong> dependencies between
                                      entities
                                    </div>
                                  </li>
                                </ul>

                                <p>For each entity in the graph the primary-key and the
                                type (as table name) is stored. The <em>birthday</em> of
                                an entity is the day of the insertion into the
                                graph.<br />
                                <br />
                                <br /></p>
                              </div>

                              <h4><a name=
                              "berechnung_des_transitiven_abschlusses">Finding the
                              transitive closure</a> &nbsp;</h4>The graph will be
                              constructed day after day (step-by-step) starting at day 1:

                              <div class="level3">
                                <ul>
                                  <li class="level1">
                                    <div class="li">
                                      at day 1 all subject entities are inserted into the
                                      graph<br />
                                    </div>
                                  </li>

                                  <li class="level1">at day <big style=
                                  "font-style: italic;"><span style=
                                  "font-family: monospace;"><font size=
                                  "4">n</font></span></big> all entities referenced by
                                  one of the entities born at day <big style=
                                  "font-style: italic; font-family: monospace;"><font size="4">
                                  n-1</font></big> are inserted into the graph.
                                  Dependencies between entities are stored.<br /></li>
                                </ul>

                                <p>The <em>progression set</em> of a day is the set of
                                all tables from which entities are inserted at that day.
                                The progession set of day 1 is the subject set. The
                                process stops if the progression set of a day is
                                empty.<br />
                                <br /></p>
                              </div>

                              <h5>Algorithm</h5>

                              <div style="font-family: arial;" class="level4">
                                &nbsp;&nbsp; 1. insert <font style="font-style: italic;"
                                size="4"><small>subject</small></font> into
                                graph&nbsp;<br />
                                <small><small><em><font size="4"><small>&nbsp;&nbsp; 2.
                                progression-set := { subject
                                }</small></font></em></small>&nbsp;</small><br />
                                &nbsp;&nbsp; 3. while <em><font size=
                                "4"><small>progression-set !=
                                {}</small></font></em><small>&nbsp;</small><br />
                                <em><font size=
                                "4"><small>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                1. progression-of-today := {}</small></font></em><br />
                                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.
                                for all (restricted) associations <em>A between tables
                                T<small><font size="2">1</font></small> and
                                T<small><font size="2">2</font></small></em><span style=
                                "font-style: italic;">, if</span><em>T<small><font size=
                                "2">1</font></small></em> or <em>T<small><font size=
                                "2">2</font></small></em> in
                                <em>progression-set:</em><br />
                                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                1. if <em>T<small><font size="2">2</font></small></em>
                                <span style="font-style: italic;">(T<small><font size=
                                "2">1</font></small>)</span> depends on
                                <em>T<small><font size="2">1
                                <big>(T<small>2</small>)</big></font></small></em>:<br />
                                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                add dependencies from <em>T<small><font size=
                                "2">2</font></small></em>(T<small><font size=
                                "2">1</font></small>)-entities born yesterday<br />
                                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                to the entities they are associated with according to
                                <span style="font-style: italic;">A</span><br />
                                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                2. insert all entities of <em>T<small><font size=
                                "2">1</font></small> (T<small><font size=
                                "2">2</font></small>)</em>, which are associated
                                according to <span style="font-style: italic;">A</span>
                                with an entity of <span style=
                                "font-style: italic;">T<small><font size=
                                "2">2</font></small> (T<small><font size=
                                "2">1</font></small>)</span> born yesterday

                                <div class="li">
                                  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                  3. if at least one entity of T<small><font size=
                                  "2">1</font></small> (T<small><font size=
                                  "2">2</font></small>) is inserted then <em><font size=
                                  "4"><small>progression-of-today := progression-of-today
                                  + {&nbsp;</small></font></em><font size=
                                  "4"><small>T</small></font><small>1</small> <font size=
                                  "4"><small>(T</small></font><small>2</small><font size=
                                  "4"><small>)</small></font> <em><font size=
                                  "4"><small>}</small></font></em>
                                </div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                                <em><font size="4"><small>3. progression-set :=
                                progression-of-today</small></font></em>
                              </div><br />

                              <div class="level4">
                                <p><font size="4">&nbsp; Example<br /></font></p>

                                <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Subject = <em>{
                                A<small><font size="2">1</font></small></em>,
                                <em>A<small><font size="2">2</font></small></em>
                                }<br /></p>

                                <table style="width: 100%; text-align: left;" border="0"
                                cellpadding="2" cellspacing="2">
                                  <tbody>
                                    <tr>
                                      <td style="vertical-align: top;">
                                      &nbsp;&nbsp;<br /></td>

                                      <td style="vertical-align: top;"><img style=
                                      "border: 0px solid ;" alt="desing" src=
                                      "design-Dateien/example.png" /></td>
                                    </tr>
                                  </tbody>
                                </table>

                                <p><br /></p>

                                <p><br />
                                <br /></p>

                                <table class="inline" border="1" cellpadding="2"
                                cellspacing="0">
                                  <tbody>
                                    <tr>
                                      <td style="background-color: rgb(153, 255, 255);">
                                      <strong>day</strong></td>

                                      <td style="background-color: rgb(153, 255, 255);">
                                      <strong>progression set</strong></td>

                                      <td style="background-color: rgb(153, 255, 255);">
                                      <strong>entities</strong></td>

                                      <td style="background-color: rgb(153, 255, 255);">
                                      <strong>dependencies</strong></td>
                                    </tr>

                                    <tr>
                                      <td>1</td>

                                      <td>A</td>

                                      <td>A1, A2</td>

                                      <td><br /></td>
                                    </tr>

                                    <tr>
                                      <td>2</td>

                                      <td>B, C</td>

                                      <td>+ B1, B2, C3, C4</td>

                                      <td>A2-&gt;C3, A2-&gt;C4</td>
                                    </tr>

                                    <tr>
                                      <td>3</td>

                                      <td>B, C</td>

                                      <td>+ C1, C2, B3, B5</td>

                                      <td>+ B1-&gt;C1, B1-&gt;C2</td>
                                    </tr>

                                    <tr>
                                      <td>4</td>

                                      <td>B</td>

                                      <td>+ B6</td>

                                      <td><em>no change</em></td>
                                    </tr>

                                    <tr>
                                      <td>5</td>

                                      <td><br /></td>

                                      <td><em>no change</em></td>

                                      <td><em>no change</em></td>
                                    </tr>
                                  </tbody>
                                </table>
                              </div>

                              <h4><br /></h4>

                              <h4><a name="sql-anweisungen">SQL-Statements</a> </h4>

                              <p>Line 1, insert subject<br />
                              <span style="font-family: monospace;"><br />
                              INSERT INTO JAILER_ENTITY(PK, birthday, type)</span><br style=
                              "font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp; SELECT
                              &lt;subject&gt;.PK, 1, '&lt;subject&gt;'</span><br style=
                              "font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp; FROM
                              &lt;subject&gt;</span><br style=
                              "font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp;&nbsp;&nbsp;
                              WHERE (&lt;condition&gt; AND 103</span><br style=
                              "font-family: monospace;" />
                              <span style=
                              "font-family: monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              NOT EXISTS (SELECT * FROM JAILER_ENTITY WHERE
                              &lt;subject&gt;.PK=Entity.PK)</span><br style=
                              "font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp;&nbsp;&nbsp;
                              [fetch first &lt;limit&gt; rows only]</span><br style=
                              "font-family: monospace;" />
                              <br />
                              <br />
                              Line 3.2.1, adding dependencies<br />
                              <br />
                              <span style="font-family: monospace;">INSERT INTO
                              JAILER_DEPENDENCY(from_PK, to_PK)</span><br style=
                              "font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp; SELECT
                              &lt;T2&gt;.PK, &lt;T1&gt;.PK</span><br style=
                              "font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp; FROM JAILER_ENTITY
                              E1, JAILER_ENTITY E2,</span><br style="font-family: monospace;" />
                              <span style=
                              "font-family: monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              &lt;T2&gt; JOIN &lt;T1&gt; ON
                              &lt;join-condition&gt;</span><br style=
                              "font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp; WHERE
                              E1.type='&lt;T1&gt;' AND
                              E2.type='&lt;T2&gt;'</span><br style=
                              "font-family: monospace;" />
                              <span style=
                              "font-family: monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              AND E1.PK=&lt;T1&gt;.PK</span><br style=
                              "font-family: monospace;" />
                              <span style=
                              "font-family: monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              AND E2.PK=&lt;T2&gt;.PK<br />
                              <br />
                              <br /></span> Line 3.2.2, adding referenced entities<br />
                              <br />
                              <span style="font-family: monospace;">INSERT INTO
                              JAILER_ENTITY(PK, birthday, type)</span><br style=
                              "font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp; SELECT
                              DISTINCT &lt;T2&gt;.PK, &lt;today&gt;,
                              '&lt;T2&gt;'</span><br style="font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp; FROM
                              &lt;T1&gt; JOIN &lt;T2&gt; ON
                              &lt;join-condition&gt;,</span><br style=
                              "font-family: monospace;" />
                              <span style=
                              "font-family: monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              Entity</span><br style="font-family: monospace;" />
                              <span style="font-family: monospace;">&nbsp; WHERE
                              &lt;T1&gt;.PK=JAILER_ENTITY.PK AND JAILER_ENTITY.birthday =
                              &lt;yesterday&gt;</span><br style=
                              "font-family: monospace;" />
                              <span style=
                              "font-family: monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              AND NOT EXISTS</span><br style="font-family: monospace;" />
                              <span style=
                              "font-family: monospace;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                              (SELECT * FROM JAILER_ENTITY WHERE
                              &lt;T2&gt;.PK=JAILER_ENTITY.PK)</span><br style=
                              "font-family: monospace;" />
                              <br /></p>
                            </td>
                          </tr>
                        </tbody>
                      </table>
                    </div>

                    <p>&nbsp;</p>
                  </td>
                </tr>

                <tr>
                  <td height="12" valign="top" width="14%"><br /></td>

                  <td><br /></td>
                </tr>
              </tbody>
            </table>
          </td>
        </tr>
      </tbody>
    </table>
  </div>
</body>
</html>
